sql server 游标的用法

2023-03-25 05:51| 来源: 网络整理| 查看: 265

将A数据库中的一些表的数据迁移到B数据库中：

-----1.将部分临时数据从系统表中清除 IF EXISTS ( SELECT * FROM sys.objects WHERE NAME = 'ps_move_history_data' ) BEGIN DROP PROCEDURE ps_move_history_data ; END GO CREATE PROCEDURE ps_move_history_data ( @procedureDefId INT , @jobScheduleId INT , @procedureExecLogId INT , @rowcount INT OUTPUT , @succount INT OUTPUT ) AS BEGIN -----初始化字段 DECLARE @data_move_id INT DECLARE @from_table NVARCHAR(200) DECLARE @desc_table NVARCHAR(200) DECLARE @desc_datasource NVARCHAR(200) DECLARE @move_fields NVARCHAR(2000) DECLARE @is_delete_all INTEGER DECLARE @condition NVARCHAR(1000) DECLARE @sql NVARCHAR(4000) DECLARE @del_keyword NVARCHAR(100) -----创建游标循环从表中将数据插入到目标库中 -----转移的记录数记录到data_move_list表中 DECLARE temp_move_table_Cursor CURSOR FOR SELECT data_move_list_id , from_table_name , desc_data_source , desc_table_name , move_fields , condition , is_delete_all FROM data_move_list WHERE active_flag = 1 OPEN temp_move_table_Cursor FETCH NEXT FROM temp_move_table_Cursor INTO @data_move_id, @from_table, @desc_datasource, @desc_table, @move_fields, @condition, @is_delete_all WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRANSACTION BEGIN TRY IF ISNULL(@desc_datasource, '') '' SET @desc_table = @desc_datasource + '.dbo.' + @desc_table SET @sql = 'INSERT INTO ' + @desc_table + '(' + @move_fields + ')' SET @sql = @sql + 'SELECT ' + @move_fields + ' FROM ' + @from_table + ' WITH(NOLOCK) ' + ISNULL(@condition, '') EXEC sp_executesql @sql UPDATE dbo.data_move_list SET last_move_time = GETDATE() , move_records = @@ROWCOUNT WHERE data_move_list_id = @data_move_id --数据迁移后，删除迁移的数据 IF ( @is_delete_all = 1 ) BEGIN SET @del_keyword = 'TRUNCATE TABLE ' + @from_table END ELSE BEGIN SET @del_keyword = ' DELETE FROM ' + @from_table + ' ' + ISNULL(@condition, '') END EXEC sp_executesql @del_keyword END TRY BEGIN CATCH BEGIN IF @@TRANCOUNT > 0 ROLLBACK INSERT INTO crm_error ( [message] , error_source , error_type , error_time , trace_info ) VALUES ( ERROR_MESSAGE() , 'ps_move_history_data' , 'Procedure Run Error' , GETDATE() , ERROR_MESSAGE() ) END END CATCH IF @@TRANCOUNT > 0 COMMIT FETCH NEXT FROM temp_move_table_Cursor INTO @data_move_id, @from_table, @desc_datasource, @desc_table, @move_fields, @condition, @is_delete_all END CLOSE temp_move_table_Cursor ; DEALLOCATE temp_move_table_Cursor ; END

【本文地址】

公司简介

联系我们